﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using HHL_Acc.HHL_DaoAcc.DataBase;
using HHL_Acc.HHL_EntityAcc;

namespace HHL_Acc.HHL_DaoAcc.DataObject
{
    internal class SmiSoldMonthlyTicketsDao : CBaseDao
    {
        public IList<SmiSoldMonthlyTickets> GetSmiSoldMonthlyTickets()
        {
            string sql =
                @"Select ROW_NUMBER() OVER (ORDER BY sold.SoldID) as Idx, sold.Serial as Serial, sold.TicketPrice as TicketPrice, ticket.TicketName as TicketIDName,
                sold.StationID as StationID, vehicle.VehicleName as VehicleIDName,sold.Start_Date as Start_Date,sold.End_Date as End_Date,sold.PlateNumber as PlateNumber,sold.RegisNumber as RegisNumber,sold.Notes as Notes,sold.VehicleOwner as VehicleOwner,
                sold.Address as Address,sold.Tel as Tel from Smi_SoldMonthlyTickets sold INNER JOIN SYS_Vehicles vehicle On sold.VehicleID=vehicle.VehicleID 
                INNER JOIN SYS_TICKETS ticket On sold.TicketID=ticket.TicketID";
            DataSet ds = ExcuteSql(sql);
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                return CDb.MapList<SmiSoldMonthlyTickets>(ds.Tables[0]);
            }

            return null;
        }

        #region "kiểm tra vé có tồn tại trong kho của nhân viên không"

        public bool CheckOwnerSerial(string strSerial, string strOwner)
        {
            string sql = @"Select * from Smi_MonthlyTicket Where Serial='" + strSerial + @"' AND Owner='" + strOwner +
                         @"'";
            DataSet ds = ExcuteSql(sql);
            if (ds == null)
                return false;
            if (ds.Tables[0].Rows.Count > 0)
                return true;
            else
                return false;
            //return list;
        }

        #endregion

        #region "Insert thông tin bán vé tháng"

        public int InsertSmiSoldMonthlyTickets(SmiSoldMonthlyTickets sold)
        {
            const String sql =
                "INSERT Smi_SoldMonthlyTickets(SoldID,Login,Serial,Sold_Date,Sold_Hour,Start_Date,End_Date,PlateNumber,TicketCode,TicketPrice,Status,Disabled,RegisNumber,Notes,VehicleOwner,Address,Tel,VehicleID,TicketID,StationID) VALUES (@SoldID,@Login,@Serial,@Sold_Date,@Sold_Hour,@Start_Date,@End_Date,@PlateNumber,@TicketCode,@TicketPrice,@Status,@Disabled,@RegisNumber,@Notes,@VehicleOwner,@Address,@Tel,@VehicleID,@TicketID,@StationID)";
            SqlParameter[] paramsSql = new SqlParameter[20];
            paramsSql[0] = new SqlParameter("@SoldID", sold.SoldID);
            paramsSql[1] = new SqlParameter("@Login", sold.Login);
            paramsSql[2] = new SqlParameter("@Serial", sold.Serial);
            paramsSql[3] = new SqlParameter("@Sold_Date", sold.Sold_Date);
            paramsSql[4] = new SqlParameter("@Sold_Hour", sold.Sold_Hour);
            paramsSql[5] = new SqlParameter("@Start_Date", sold.Start_Date);
            paramsSql[6] = new SqlParameter("@End_Date", sold.End_Date);
            paramsSql[7] = new SqlParameter("@PlateNumber", sold.PlateNumber);
            paramsSql[8] = new SqlParameter("@TicketCode", sold.TicketCode);
            paramsSql[9] = new SqlParameter("@TicketPrice", sold.TicketPrice);
            paramsSql[10] = new SqlParameter("@Status", sold.Status);
            paramsSql[11] = new SqlParameter("@Disabled", sold.Disabled);
            paramsSql[12] = new SqlParameter("@RegisNumber", sold.RegisNumber);
            paramsSql[13] = new SqlParameter("@Notes", sold.Notes);
            paramsSql[14] = new SqlParameter("@VehicleOwner", sold.VehicleOwner);
            paramsSql[15] = new SqlParameter("@Address", sold.Address);
            paramsSql[16] = new SqlParameter("@Tel", sold.Tel);
            paramsSql[17] = new SqlParameter("@VehicleID", sold.VehicleID);
            paramsSql[18] = new SqlParameter("@TicketID", sold.TicketID);
            paramsSql[19] = new SqlParameter("@StationID", sold.StationID);
            return ExcuteNoneQuery(sql, paramsSql);
        }

        #endregion

        #region "Xóa thông tin vé trong bảng Smi_MonthlyTickets"

        public int DeleteSmiMonthlyTickets(String strSerial)
        {
            String sql = "Delete from Smi_MonthlyTicket where Serial=@Serial";
            SqlParameter[] paramsSql = new SqlParameter[1];
            paramsSql[0] = new SqlParameter("@Serial", strSerial);
            return ExcuteNoneQuery(sql, paramsSql);
        }

        #endregion

        #region "Cập nhật thông tin bán vé tháng"

        public int UpdateSmiSoldMonthlyTickets(SmiSoldMonthlyTickets sold)
        {
            const String sql =
                "UPDATE Smi_SoldMonthlyTickets Set Login=@Login,Sold_Date=@Sold_Date,Sold_Hour=@Sold_Hour,Start_Date=@Start_Date,End_Date=@End_Date,PlateNumber=@PlateNumber,RegisNumber=@RegisNumber,Notes=@Notes,VehicleOwner=@VehicleOwner,Address=@Address,Tel=@Tel Where Serial=@Serial";
            SqlParameter[] paramsSql = new SqlParameter[12];
            paramsSql[0] = new SqlParameter("@Login", sold.Login);
            paramsSql[1] = new SqlParameter("@Sold_Date", sold.Sold_Date);
            paramsSql[2] = new SqlParameter("@Sold_Hour", sold.Sold_Hour);
            paramsSql[3] = new SqlParameter("@Start_Date", sold.Start_Date);
            paramsSql[4] = new SqlParameter("@End_Date", sold.End_Date);
            paramsSql[5] = new SqlParameter("@PlateNumber", sold.PlateNumber);
            paramsSql[6] = new SqlParameter("@RegisNumber", sold.RegisNumber);
            paramsSql[7] = new SqlParameter("@Notes", sold.Notes);
            paramsSql[8] = new SqlParameter("@VehicleOwner", sold.VehicleOwner);
            paramsSql[9] = new SqlParameter("@Address", sold.Address);
            paramsSql[10] = new SqlParameter("@Tel", sold.Tel);
            paramsSql[11] = new SqlParameter("@Serial", sold.Serial);
            return ExcuteNoneQuery(sql, paramsSql);
        }

        #endregion
    }
}